package com.lagou.module4.dao;

import com.lagou.module4.model.Student;
import com.lagou.module4.util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class StudentDaoImp implements StudentDao {
    @Override
    //增加学生，用0或1代表成功或失败
    public int addStudent(Student student) {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = DbUtil.getConnection();
            //2 准备sql语句
            String sql = "insert into StudentInfo values ("+student.getID()+",?,?,?,?,?)";
            //3 执行sql语句并获取结果
            ps = connection.prepareStatement(sql);
            ps.setString(1, student.getName());
            ps.setString(2, student.getSex());
            ps.setString(3, student.getBirth_date());
            ps.setString(4, student.getEmail());
            ps.setString(5, student.getComment());
            System.out.println(sql);
            System.out.println(student);
            int row = ps.executeUpdate();
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //4 释放资源
            try {
                DbUtil.closeResource(connection, ps);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }
    @Override
    public int changeStudent(Student student) {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = DbUtil.getConnection();

            //2 准备sql语句
            String sql = "update StudentInfo set name=?,sex=?,birth_date=?,email=?,comment=? where ID="+student.getID();
            //3 执行sql语句并获取结果
            ps = connection.prepareStatement(sql);
            ps.setString(1, student.getName());
            ps.setString(2, student.getSex());
            ps.setString(3, student.getBirth_date());
            ps.setString(4, student.getEmail());
            ps.setString(5, student.getComment());
            System.out.println(student);
            System.out.println(sql);
            int row = ps.executeUpdate();
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //4 释放资源
            try {
                DbUtil.closeResource(connection, ps);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public int removeStudent(int id) {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = DbUtil.getConnection();

            //2 准备sql语句
            String sql = "delete from StudentInfo where ID = ?";
            //3 执行sql语句并获取结果
            ps = connection.prepareStatement(sql);
            ps.setInt(1, id);
            int row = ps.executeUpdate();
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //4 释放资源
            try {
                DbUtil.closeResource(connection, ps);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public Student searchStudent(int id) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            connection = DbUtil.getConnection();

            //2 准备sql语句
            String sql = "select * from StudentInfo where ID = ?";
            //3 执行sql语句并获取结果
            ps = connection.prepareStatement(sql);
            ps.setInt(1, id);
            resultSet = ps.executeQuery();
            if (resultSet.next()){
                Student student = new Student(
                        resultSet.getInt("ID"),
                        resultSet.getString("name"),
                        resultSet.getString("sex"),
                        resultSet.getString("birth_date"),
                        resultSet.getString("email"),
                        resultSet.getString("comment")
                );
                return student;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //4 释放资源
            try {
                DbUtil.closeResource(connection, ps);
                if(null != resultSet) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
}
